import pymysql


def permanentPopulation():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()

    sql = "SELECT * from population where type='年末常住人口数' ORDER BY year"
    cur.execute(sql)
    datas = cur.fetchall()
    years = [i['year'] for i in datas]
    city = [i['city'] for i in datas]
    number = list(round(float(i['number']), 2) for i in datas)
    data = []
    for i in range(len(number)):
        data.append({'year': years[i], 'city': city[i], 'population': number[i]})
    data = {'data': data}
    print(data)
    cur.close()
    conn.close()
    return data


def grossDomesticProduct():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()

    sql = "SELECT * from economic where type='地区生产总值' ORDER BY year"
    cur.execute(sql)
    datas = cur.fetchall()
    years = [i['year'] for i in datas]
    city = [i['city'] for i in datas]
    number = list(round(float(i['number']), 2) for i in datas)
    data = []
    for i in range(len(number)):
        data.append({'year': years[i], 'city': city[i], 'population': number[i]})
    data = {'data': data}
    print(data)
    cur.close()
    conn.close()
    return data


def disposableIncome():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()

    sql = "SELECT * from economic where type='全体居民人均可支配收入' ORDER BY year"
    cur.execute(sql)
    datas = cur.fetchall()
    years = [i['year'] for i in datas]
    city = [i['city'] for i in datas]
    number = list(round(float(i['number']), 2) for i in datas)
    data = []
    for i in range(len(number)):
        data.append({'year': years[i], 'city': city[i], 'population': number[i]})
    data = {'data': data}
    print(data)
    cur.close()
    conn.close()
    return data


def getMapData():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()

    cityList = ['广州市', '深圳市', '珠海市', '汕头市', '佛山市', '韶关市', '河源市', '梅州市', '惠州市', '汕尾市', '东莞市',
           '中山市', '江门市', '阳江市', '湛江市', '茂名市', '肇庆市', '清远市', '潮州市', '揭阳市', '云浮市']
    data = {}
    for i in cityList:
        data[i] = []

    sql = ("SELECT * from population where type='年末常住人口数' and year=2022 and city!='广东省' and city!='珠三角'"
           "and city!='东翼' and city!='西翼' and city!='山区'")
    cur.execute(sql)
    datas = cur.fetchall()
    numbers = [str(i['number']) + '万人' for i in datas]
    city = [i['city'] + '市' for i in datas]
    for i in range(len(numbers)):
        data[city[i]].append(numbers[i])

    sql = ("SELECT * from economic where type='地区生产总值' and year=2022 and city!='广东省' and city!='珠三角'"
           "and city!='东翼' and city!='西翼' and city!='山区'")
    cur.execute(sql)
    datas = cur.fetchall()
    numbers = [str(i['number']) + '亿元' for i in datas]
    city = [i['city'] + '市' for i in datas]
    for i in range(len(numbers)):
        data[city[i]].append(numbers[i])

    sql = ("SELECT * from environment where type='AQI达标率' and year=2022 and city!='广东省' and city!='珠三角'"
           "and city!='东翼' and city!='西翼' and city!='山区'")
    cur.execute(sql)
    datas = cur.fetchall()
    numbers = [str(i['number']) + '%' for i in datas]
    city = [str(i['city']).replace('\ufeff', '') + '市' for i in datas]
    for i in range(len(numbers)):
        data[city[i]].append(numbers[i])

    sql = ("SELECT * from economic where type='全体居民人均消费支出' and year=2022 and city!='广东省' and city!='珠三角'"
           "and city!='东翼' and city!='西翼' and city!='山区'")
    cur.execute(sql)
    datas = cur.fetchall()
    numbers = [str(i['number']) + '元' for i in datas]
    city = [i['city'] + '市' for i in datas]
    for i in range(len(numbers)):
        data[city[i]].append(numbers[i])

    print(data)
    cur.close()
    conn.close()
    return data


if __name__ == '__main__':
    permanentPopulation()
